import sqlalchemy
from sqlalchemy.sql import and_, or_

engine = sqlalchemy.create_engine('mysql://root:ZHabc123.@shc.chenzao.fun:13306/testdb', echo=True)

# 创建表
meta_data = sqlalchemy.MetaData()

person_table = sqlalchemy.Table(
    'person', meta_data,
    sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column('name', sqlalchemy.String(128), unique=True, nullable=False),
    sqlalchemy.Column('birthday', sqlalchemy.Date, nullable=False),
    sqlalchemy.Column('address', sqlalchemy.String(255), nullable=True),
)


meta_data.create_all(engine)

# 查询
with engine.connect() as conn:
    # query = person_table.select().where(person_table.c.birthday > '1980-5-23').where(person_table.c.id < 4)
    query = person_table.select().where(
        or_(
            person_table.c.name == 'Tom',
            and_(person_table.c.birthday > '1980-5-23', person_table.c.id < 4)
        )
    )
    # result 会得到一个可迭代的对象的结果集，返回别的不是真正的结果集数据，
    # sql并没有帮你真正提取出来全部的的数据，而是返回一个迭代器，通过for循环可以每次取出来一条数据
    # 防止结果数据太多，撑爆内存
    result_set = conn.execute(query)
    for row in result_set:
        print(row[0])
        print(row.name)

    # 如果数据少可以使用 fetchall 一次性获取全部数据
    # result = result_set.fetchall()
    # print(result)
    
    # 只返回第一条数据
    # row = result_set.fetchone()
    # print(row)
    
# 更新删除
with engine.connect() as conn:
    # 更新数据
    # update_query = person_table.update().values(address='aaa')
    update_query = person_table.update().values(address='上海').where(person_table.c.id==4)
    conn.execute(update_query)
    conn.commit()
    
    # 删除数据
    # conn.execute(person_table.delete().where(person_table.c.id == 2))
    # conn.commit()
    
    

    
    
      